"""数据库工具类"""

import logging
import pymysql
from typing import List, Dict, Any, Optional, Set
from pymysql.cursors import DictCursor
from datetime import datetime
from config.db_config import DB_CONFIG
from utils.logger import sync_logger

logger = logging.getLogger(__name__)

class Database:
    def __init__(self, host: str = None, port: int = None, user: str = None, 
                 password: str = None, database: str = None):
        self.host = host or DB_CONFIG['host']
        self.port = port or DB_CONFIG['port']
        self.user = user or DB_CONFIG['user']
        self.password = password or DB_CONFIG['password']
        self.database = database or DB_CONFIG['database']
        self.connection = None
        
    def connect(self):
        """建立数据库连接"""
        try:
            self.connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                database=self.database,
                cursorclass=DictCursor
            )
        except Exception as e:
            logger.error(f"数据库连接失败: {str(e)}")
            raise
            
    def disconnect(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()
            self.connection = None
            
    def execute_query(self, query: str, params: Optional[tuple] = None) -> List[Dict[str, Any]]:
        """执行查询"""
        try:
            if not self.connection:
                self.connect()
                
            with self.connection.cursor() as cursor:
                cursor.execute(query, params or ())
                return cursor.fetchall()
        except Exception as e:
            logger.error(f"查询执行失败: {str(e)}")
            raise
            
    def execute_update(self, query: str, params: Optional[tuple] = None) -> int:
        """执行更新"""
        try:
            if not self.connection:
                self.connect()
                
            with self.connection.cursor() as cursor:
                affected_rows = cursor.execute(query, params or ())
                self.connection.commit()
                return affected_rows
        except Exception as e:
            logger.error(f"更新执行失败: {str(e)}")
            self.connection.rollback()
            raise
            
    def _map_field_type(self, feishu_type, sample_value=None):
        """将飞书字段类型映射为MySQL字段类型
        
        Args:
            feishu_type: 飞书字段类型
            sample_value: 样本值，用于辅助判断类型
        """
        # 如果有样本值，优先使用样本值判断类型
        if sample_value is not None:
            if isinstance(sample_value, bool):
                return "TINYINT(1)"
            elif isinstance(sample_value, int):
                # 判断是否为时间戳（13位毫秒级时间戳）
                if len(str(sample_value)) >= 13:
                    return "BIGINT"
                elif len(str(sample_value)) >= 10:  # 10位秒级时间戳
                    return "INT"
                else:
                    return "INT"
            elif isinstance(sample_value, float):
                return "FLOAT"
            elif isinstance(sample_value, (list, dict)):
                return "TEXT"
            elif isinstance(sample_value, str):
                # 检查字符串是否为时间戳
                try:
                    int_value = int(sample_value)
                    if len(str(int_value)) >= 13:  # 13位毫秒级时间戳
                        return "BIGINT"
                    elif len(str(int_value)) >= 10:  # 10位秒级时间戳
                        return "INT"
                except (ValueError, TypeError):
                    pass
                
                # 如果字符串长度超过255，使用TEXT
                if len(sample_value) > 255:
                    return "TEXT"
                else:
                    return "VARCHAR(255)"
        
        # 使用飞书字段类型映射
        type_mapping = {
            1: "VARCHAR(255)",  # 文本
            2: "INT",           # 数字
            3: "BIGINT",        # 日期时间
            4: "TEXT",          # 多行文本
            5: "VARCHAR(50)",   # 选项
            6: "TEXT",          # 多选
            7: "TEXT",          # 人员
            8: "TEXT",          # 附件
            9: "TEXT",          # 关联记录
            10: "TEXT",         # 公式
            11: "VARCHAR(255)", # 自动编号
            12: "BIGINT",       # 创建时间
            13: "BIGINT",       # 修改时间
            14: "TEXT",         # 创建人
            15: "TEXT",         # 修改人
        }
        return type_mapping.get(feishu_type, "TEXT")  # 默认TEXT

    def create_table(self, table_name: str, columns: List[Dict[str, Any]]) -> None:
        """创建表"""
        try:
            # 构建CREATE TABLE语句
            column_defs = []
            # 查找主键字段
            primary_key_field = None
            for col in columns:
                name = col.get('field_name') or col.get('name')
                if name.lower() in ['record_id', 'id', '_id', '编号']:
                    primary_key_field = name
                    
            # 添加自增ID作为主键
            column_defs.append("`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY")
                    
            for col in columns:
                name = col.get('field_name') or col.get('name')
                # 处理特殊字符和MySQL关键字
                name = name.replace('`', '``')  # 转义反引号
                
                # 对于所有字段统一使用TEXT类型
                type_ = "TEXT"
                
                nullable = "NULL"
                column_defs.append(f"`{name}` {type_} {nullable}")
                
            create_table_sql = f"""
            CREATE TABLE IF NOT EXISTS `{table_name}` (
                {', '.join(column_defs)}
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            """
            self.execute_update(create_table_sql)
            logger.info(f"表 {table_name} 创建成功")
        except Exception as e:
            logger.error(f"创建表 {table_name} 失败: {str(e)}")
            raise
            
    def insert_records(self, table_name: str, records: List[Dict[str, Any]]) -> int:
        """插入记录"""
        if not records:
            return 0
            
        try:
            # 获取字段名
            fields = list(records[0].keys())
            
            # 构建INSERT语句
            placeholders = ', '.join(['%s'] * len(fields))
            field_names = ', '.join([f'`{f}`' for f in fields])
            insert_sql = f"""
            INSERT INTO `{table_name}` ({field_names})
            VALUES ({placeholders})
            """
            
            # 准备数据
            values = [[record[field] for field in fields] for record in records]
            
            # 执行批量插入
            with self.connection.cursor() as cursor:
                affected_rows = cursor.executemany(insert_sql, values)
                self.connection.commit()
                return affected_rows
        except Exception as e:
            logger.error(f"插入记录失败: {str(e)}")
            self.connection.rollback()
            raise
            
    def _parse_datetime(self, date_str: Optional[str]) -> Optional[datetime]:
        """安全地解析日期时间字符串"""
        if not date_str:
            return None
        try:
            return datetime.fromisoformat(date_str.replace('Z', '+00:00'))
        except (ValueError, AttributeError):
            return None
            
    def _batch_save(self, table: str, data: List[Dict[str, Any]], 
                   id_field: str, fields: List[str], 
                   values_func: callable, 
                   existing_ids: Set = None,
                   project_id: int = None) -> None:
        """通用批量保存方法"""
        if not data:
            return
        
        with sync_logger.start_timer(f"批量保存{table}数据"):
            try:
                # 如果ID字段是自增ID，那么不需要去查询已存在记录
                if id_field == 'id' and id_field not in data[0]:
                    # 直接插入所有记录
                    sync_logger.log_info(f"使用自增ID作为主键，将插入所有 {len(data)} 条记录")
                    
                    # 准备SQL语句 - 所有字段名使用反引号
                    placeholders = ', '.join(['%s'] * len(fields))
                    quoted_fields = [f"`{field}`" for field in fields]
                    sql = f"""
                        INSERT INTO `{table}` ({', '.join(quoted_fields)})
                        VALUES ({placeholders})
                    """
                    
                    # 准备批量数据
                    values = []
                    for item in data:
                        try:
                            values.append(values_func(item))
                        except Exception as e:
                            sync_logger.log_error(f"处理记录时出错: {str(e)}")
                            continue
                    
                    if not values:
                        sync_logger.log_error(f"没有有效的数据可以保存")
                        return
                    
                    # 批量执行
                    with self.connection.cursor() as cursor:
                        cursor.executemany(sql, values)
                        self.connection.commit()
                        sync_logger.log_info(f"成功保存 {len(values)} 条{table}数据到数据库")
                    return
                
                # 自动识别主键字段
                if id_field == 'id':
                    for key in data[0].keys():
                        if key in ['_id', 'record_id', 'id']:
                            id_field = key
                            break
                
                # 获取已存在的数据ID
                try:
                    if existing_ids is None:
                        query = f"SELECT `{id_field}` FROM `{table}`"
                        if project_id is not None:
                            query += f" WHERE `project_id` = {project_id}"
                        existing_ids = {row[id_field] for row in self.execute_query(query)}
                except Exception as e:
                    sync_logger.log_error(f"查询已有ID失败，可能是表不存在: {str(e)}")
                    existing_ids = set()  # 如果查询失败（例如表不存在），假设没有已存在的记录
                
                # 过滤新数据和更新数据
                new_data = [d for d in data if d[id_field] not in existing_ids]
                updated_data = [d for d in data if d[id_field] in existing_ids]
                
                sync_logger.log_info(f"发现 {len(new_data)} 个新记录，{len(updated_data)} 个已存在记录")
                
                if not new_data and not updated_data:
                    sync_logger.log_info(f"没有新的{table}数据需要保存")
                    return
                
                # 准备SQL语句 - 所有字段名使用反引号
                placeholders = ', '.join(['%s'] * len(fields))
                quoted_fields = [f"`{field}`" for field in fields]
                update_fields = ', '.join([f"`{field}` = VALUES(`{field}`)" for field in fields])
                sql = f"""
                    INSERT INTO `{table}` ({', '.join(quoted_fields)})
                    VALUES ({placeholders})
                    ON DUPLICATE KEY UPDATE {update_fields}
                """
                
                # 准备批量数据
                values = []
                for item in data:
                    try:
                        values.append(values_func(item))
                    except Exception as e:
                        sync_logger.log_error(f"处理记录 {item.get(id_field, '未知')} 时出错: {str(e)}")
                        continue
                
                if not values:
                    sync_logger.log_error(f"没有有效的数据可以保存")
                    return
                
                # 批量执行
                with self.connection.cursor() as cursor:
                    cursor.executemany(sql, values)
                    self.connection.commit()
                    sync_logger.log_info(f"成功保存 {len(values)} 条{table}数据到数据库")
                    
            except Exception as e:
                logger.error(f"批量保存{table}数据时发生错误: {str(e)}")
                self.connection.rollback()
                raise 